定食屋DDL 取引管理 20220108

https://gyazo.com/9a20c5d80fa6b58ec0e89fbccb65163c

多重度の一部は演習の問題。

code:restaurant_ddl.sql

drop database if exists restaurant;

create database restaurant;

use restaurant;

create table emp_pos (

no integer(2) primary key,

name varchar(10)

);

create table emp (

no integer(4) primary key,

name varchar(10),

hiredate date,

sal integer(7),

quitdate date,

posno integer(2)

);

-- 従業員管理から追加

create table receipt (

no integer(6) primary key,

locationid integer(3),

date datetime,

empno integer(4)

);

create table item (

id integer(3) primary key,

name varchar(10),

price integer(4)

);

create table location (

id integer(3) primary key,

name varchar(10),

address varchar(100),

tel varchar(20)

);

create table receipt_detail (

receiptno integer(6),

itemid integer(3),

count integer(3),

primary key(receiptno, itemid)

);

emp_pos

emp

のテストデータ

code:restaurant_init.sql

insert into location(id, name, address, tel) values(10, 'A定食', 'F県〇〇市××', '××××-××-××××');

insert into item(id, name, price) values(1, 'そば', 300);

insert into item(id, name, price) values(2, 'てんぷら', 400);

insert into item(id, name, price) values(3, 'ざるそば', 350);

insert into item(id, name, price) values(4, 'うどん', 300);

insert into item(id, name, price) values(5, 'にくうどん', 500);

insert into item(id, name, price) values(6, 'きつねうどん', 400);

insert into item(id, name, price) values(7, '月見うどん', 400);

insert into item(id, name, price) values(8, 'カレーうどん', 500);

insert into item(id, name, price) values(9, '天ぷらうどん', 450);

insert into item(id, name, price) values(10, 'ざるそば', 300);

insert into item(id, name, price) values(11, 'ざるうどん', 300);

insert into item(id, name, price) values(12, '天ざるそば', 700);

insert into item(id, name, price) values(13, 'おにぎり', 200);

insert into item(id, name, price) values(14, 'かしわおにぎり', 300);

insert into item(id, name, price) values(15, '親子丼', 500);

insert into item(id, name, price) values(16, 'カツ丼', 600);

insert into item(id, name, price) values(17, '牛丼', 600);

insert into item(id, name, price) values(18, '天丼', 700);

insert into item(id, name, price) values(19, '海鮮丼', 800);

insert into item(id, name, price) values(20, 'さばの味噌煮定食', 800);

insert into item(id, name, price) values(21, 'さばの炭火焼き定食', 700);

insert into item(id, name, price) values(22, 'からあげ定食', 700);

insert into item(id, name, price) values(23, '焼き肉定食', 700);

insert into item(id, name, price) values(24, 'ハンバーグ定食', 800);

insert into item(id, name, price) values(25, 'カレー', 500);

insert into item(id, name, price) values(26, '野菜炒め定食', 650);

insert into receipt(no, locationid, date, empno) values(1053, 10, '2021-11-15 12:10:10', 3004);

insert into receipt(no, locationid, date, empno) values(1054, 10, '2021-11-15 12:20:10', 3004);

insert into receipt(no, locationid, date, empno) values(1055, 10, '2021-11-15 12:40:10', 2002);

insert into receipt(no, locationid, date, empno) values(1056, 10, '2021-11-15 13:00:10', 3002);

insert into receipt(no, locationid, date, empno) values(1057, 10, '2021-11-15 13:20:10', 4006);

insert into receipt(no, locationid, date, empno) values(1058, 10, '2021-11-15 13:45:10', 4004);

insert into receipt(no, locationid, date, empno) values(1059, 10, '2021-11-15 14:10:00', 1001);

insert into receipt(no, locationid, date, empno) values(1060, 10, '2021-12-28 17:00:00', 4003);

insert into receipt(no, locationid, date, empno) values(1062, 10, '2021-12-28 19:25:00', 4003);

insert into receipt(no, locationid, date, empno) values(1063, 10, '2021-12-28 20:00:25', 3003);

insert into receipt(no, locationid, date, empno) values(1071, 10, '2022-01-05 12:03:22', 4005);

insert into receipt(no, locationid, date, empno) values(1072, 10, '2022-01-05 12:20:30', 4005);

insert into receipt(no, locationid, date, empno) values(1073, 10, '2022-01-05 12:35:36', 4006);

-- 一旦ここまで 20220108

insert into receipt_detail(receiptno, itemid, count) values(1053, 25, 1);

insert into receipt_detail(receiptno, itemid, count) values(1054, 6, 1);

insert into receipt_detail(receiptno, itemid, count) values(1054, 8, 1);

insert into receipt_detail(receiptno, itemid, count) values(1055, 10, 2);

insert into receipt_detail(receiptno, itemid, count) values(1055, 14, 1);

insert into receipt_detail(receiptno, itemid, count) values(1056, 25, 1);

insert into receipt_detail(receiptno, itemid, count) values(1056, 26, 1);

insert into receipt_detail(receiptno, itemid, count) values(1056, 17, 1);

insert into receipt_detail(receiptno, itemid, count) values(1057, 8, 1);

insert into receipt_detail(receiptno, itemid, count) values(1057, 15, 1);

insert into receipt_detail(receiptno, itemid, count) values(1057, 19, 1);

insert into receipt_detail(receiptno, itemid, count) values(1057, 12, 1);

insert into receipt_detail(receiptno, itemid, count) values(1058, 23, 1);

insert into receipt_detail(receiptno, itemid, count) values(1059, 24, 1);

insert into receipt_detail(receiptno, itemid, count) values(1060, 6, 1);

insert into receipt_detail(receiptno, itemid, count) values(1060, 7, 1);

insert into receipt_detail(receiptno, itemid, count) values(1062, 26, 1);

insert into receipt_detail(receiptno, itemid, count) values(1063, 12, 1);

insert into receipt_detail(receiptno, itemid, count) values(1063, 20, 1);

insert into receipt_detail(receiptno, itemid, count) values(1063, 21, 2);

insert into receipt_detail(receiptno, itemid, count) values(1063, 9, 1);

insert into receipt_detail(receiptno, itemid, count) values(1063, 13, 1);

insert into receipt_detail(receiptno, itemid, count) values(1071, 20, 1);

insert into receipt_detail(receiptno, itemid, count) values(1072, 18, 1);

insert into receipt_detail(receiptno, itemid, count) values(1072, 19, 1);

insert into receipt_detail(receiptno, itemid, count) values(1073, 7, 1);

-- 一旦ここまで 20220108